<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Validator;
use App\Http\Constant;
use App\Helpers\ValidatorResponse;
use App\Helpers\SuccessResponse;
use Illuminate\Support\Facades\DB;

class UserController extends Controller {
    public function listUser(Request $request) {
        $is_page = intval($request->input('is_page')) == 1 ? 1 : 0;
        $show_page = intval($request->input('show_page')) == 1 ? 1 : 0;
        $current_page = intval($request->input('current_page'));
        $current_page = $current_page > 1 ? $current_page : 1;
        $page_count = intval($request['page_count']);
        $page_count = $page_count > 0 ? $page_count : 20;
        $order_by = json_decode($request->input('order_by'), true);
        $user_status = $request->input('user_status');
        if (0 != $user_status && 'pending' != $user_status) {
            $user_status = 'active';
        }
        $user_type    = $request->input('user_type');
        $starttime    = trim($request->input('starttime'));
        $endtime      = trim($request->input('endtime'));
        $keywords     = trim(strval($request->input('keywords')));
        if (0 != count($order_by)) {
            $order_by_arr = array();
            foreach ($order_by as $key => $value) {
                if ($value == 'desc' || $value == 'asc') {
                    array_push($order_by_arr, $key . ' ' . $value);
                } else {
                    continue;
                }
            }
            $order_by = implode(',', $order_by_arr);
        } else {
            $order_by = 'id desc';
        }

        if ($is_page) {
            $data_sql = 'select u.*, r.* from users as u left join role_user as r on r.user_id = u.id where 1=1';
            if (0 != strlen($keywords)) {
                $data_sql .= ' and ( email like "%' . $keywords . '%" or phone_num like "%' . $keywords . '%" or first_name like "%' . $keywords . '%" or last_name like "%' . $keywords . '%" )';
            }
            if(($starttime != "" && $endtime != "") && ($endtime >= $starttime)){
                $stTime    = $starttime.' 00:00:00';
                $edTime    = $endtime  .' 23:59:59';
                $data_sql .= ' and (u.created_at >= "' . $stTime . '" and u.created_at <= "' . $edTime . '")';
            }
            if($user_type > 0){
                $data_sql .= ' and r.role_id = '.$user_type;
            }
            if ($user_status && $user_status != '0') {
                $data_sql .= ' and u.status_key = "' . $user_status . '"';
            }
            $data_sql .= ' order by ' . $order_by;
            $limit_start = ($current_page - 1) * $page_count;
            $data_sql .= ' limit ' . $limit_start . ',' . $page_count;
            
            $user_data = DB::connection('data_api_database')->select($data_sql);
            $page_info = array(
                'total' => count($user_data),
                'total_page' => 1,
            );
            
            if ($show_page) {
                $count_sql = 'select count(u.id) as total from users as u left join role_user as r on r.user_id = u.id where 1=1';
                if (0 != strlen($keywords)) {
                    $count_sql .= ' and ( u.email like "%' . $keywords . '%" or u.phone_num like "%' . $keywords . '%" or u.first_name like "%' . $keywords . '%" or u.last_name like "%' . $keywords . '%" )';
                }
                if(($starttime != "" && $endtime != "") && ($endtime >= $starttime)){
                    $stTime    = $starttime.' 00:00:00';
                    $edTime    = $endtime  .' 23:59:59';
                    $count_sql .= ' and (u.created_at >= "' . $stTime . '" and u.created_at <= "' . $edTime . '")';
                }
                if($user_type > 0){
                    $count_sql .= ' and r.role_id = '.$user_type;
                }
                if ($user_status && $user_status != '0') {
                    $count_sql .= ' and u.status_key = "' . $user_status . '"';
                }
                $user_count = DB::connection('data_api_database')->select($count_sql);
                $user_count = json_decode(json_encode($user_count), true);
                $page_info = array(
                    'total' => $user_count[0]['total'],
                    'total_page' => ceil($user_count[0]['total'] / $page_count),
                );
            }
        } else {
            $data_sql = 'select u.*, r.* from users as u left join role_user as r on r.user_id = u.id where 1=1';
            if (0 != strlen($keywords)) {
                $data_sql .= ' and ( email like "%' . $keywords . '%" or phone_num like "%' . $keywords . '%" or first_name like "%' . $keywords . '%" or last_name like "%' . $keywords . '%" )';
            }
            if(($starttime != "" && $endtime != "") && ($endtime >= $starttime)){
                $stTime    = $starttime.' 00:00:00';
                $edTime    = $endtime  .' 23:59:59';
                $data_sql .= ' and (u.created_at >= "' . $stTime . '" and u.created_at <= "' . $edTime . '")';
            }
            if($user_type > 0){
                $data_sql .= ' and r.role_id = '.$user_type;
            }
            if ($user_status && $user_status != '0') {
                $data_sql .= ' and u.status_key = "' . $user_status . '"';
            }
            $data_sql .= ' order by ' . $order_by;
            $data_sql .= ' limit 0,999';
            $user_data = DB::connection('data_api_database')->select($data_sql);
            $page_info = array(
                'total' => count($user_data),
                'total_page' => 1,
            );
        }
        $user_data = json_decode(json_encode($user_data), true);
        $user_roles = DB::connection('data_api_database')->select('select * from roles;');
        $user_roles = json_decode(json_encode($user_roles), true);
        $role_id_role_name = [];
        foreach ($user_roles as $value) {
            $role_id_role_name[$value['id']] = $value;
        }
        foreach ($user_data as &$value) {
            $value['role_name'] = isset($role_id_role_name[$value['role_id']]) ? $role_id_role_name[$value['role_id']]['display_name'] : 'Unkown User Type';
            $value['status_key'] = ucwords($value['status_key']);
        }
        $rtn_data = array(
            'where' => array(
                'is_page' => $is_page,
                'show_page' => $show_page,
                'current_page' => $current_page,
                'page_count' => $page_count,
                'order_by' => $order_by,
                'user_status' => $user_status,
                'user_type'  => $user_type,
                'starttime'  => $starttime,
                'endtime'    => $endtime,
                'keywords' => $keywords,
            ),
            'data' => $user_data,
            'show_page' => $page_info
        );
        return SuccessResponse::handler($rtn_data);
    }
}
